package com.olap.starter.util;


import com.olap.starter.core.exception.OnlineException;
import com.olap.starter.util.StringUtils;

/**
 * @author WBG
 * @date 2021/9/6 8:09
 * @describe
 */
public class SqlUtil {
    /**
     * 仅支持字母、数字、下划线、空格、逗号、小数点（支持多个字段排序）
     */
    public static String SQL_PATTERN = "[a-zA-Z0-9_\\ \\,\\.]+";

    /**
     * 检查字符，防止注入绕过
     */
    public static String escapeOrderBySql(String value) {
        if (StringUtils.isNotEmpty(value) && !isValidOrderBySql(value)) {
            throw new OnlineException("参数不符合规范，不能进行查询");
        }
        return value;
    }

    /**
     * 验证 order by 语法是否符合规范
     */
    public static boolean isValidOrderBySql(String value) {
        return value.matches(SQL_PATTERN);
    }

    /**
     * 获取分页
     *
     * @param pageNum
     * @param pageSize
     * @return
     */
    public static int getLimitStartNumber(int pageNum, int pageSize) {
        if (pageNum == 0) {
            pageNum = 1;
        }
        return (pageNum - 1) * pageSize;
    }

    private static String[] getColumnStruct(String strColumn) {

        String columnName = null;
        String asColumnName = null;
        boolean isBreak = false;

        int index = 0;
        int startIndex = -1;
        int endIndex = -1;

        while (strColumn.indexOf("(") >= 0) {
            int leftIndex = strColumn.indexOf("(");
            if (strColumn.length() > leftIndex) {
                strColumn = strColumn.substring(leftIndex + 1);
            } else {
                break;
            }
        }

        while (!isBreak && index < strColumn.length()) {
            char ch = strColumn.charAt(index);

            if (Character.isLowerCase(ch) || Character.isUpperCase(ch) ||
                    Character.isDigit(ch) || ch == '_') {
                if (startIndex == -1) {
                    startIndex = index;
                }

            } else if (ch == '.' || ch == '(') {
                startIndex = -1;
            } else if (startIndex >= 0) {
                isBreak = true;
            }

            index++;
        }

        if (startIndex >= 0) {
            //endIndex = index-1;
            endIndex = index;
        }

        if (startIndex >= 0 && startIndex <= endIndex) {
            columnName = strColumn.substring(startIndex, endIndex).trim();


            if (endIndex < strColumn.length()) {
                strColumn = strColumn.substring(endIndex);

                while (strColumn.indexOf(")") >= 0) {
                    int rightIndex = strColumn.indexOf(")");
                    if (strColumn.length() > rightIndex) {
                        strColumn = strColumn.substring(rightIndex + 1);
                    } else {
                        break;
                    }
                }

                strColumn = replaceCaculateColumns(strColumn);

                isBreak = false;
                index = 0;
                int asStartIndex = -1;
                int asEndIndex = -1;

                while (!isBreak && index < strColumn.length()) {
                    char ch = strColumn.charAt(index);

                    if (Character.isLowerCase(ch) || Character.isUpperCase(ch) ||
                            Character.isDigit(ch) || ch == '_') {
                        if (asStartIndex == -1) {
                            asStartIndex = index;
                        }

                        if (ch == 's') {
                            if (index >= 1 && index + 1 < strColumn.length()) {
                                char beforeCh = strColumn.charAt(index - 1);
                                char afterCh = strColumn.charAt(index + 1);

                                if (beforeCh == 'a'
                                        && (afterCh == ' ' || afterCh == '\n' || afterCh == '\t' || afterCh == '\r')) {
                                    asStartIndex = -1;
                                }
                            }
                        }

                    } else if (asStartIndex >= 0) {
                        isBreak = true;
                    }
                    index++;
                }

                if (asStartIndex >= 0) {
                    asEndIndex = index;
                }

                if (asStartIndex >= 0 && asStartIndex <= asEndIndex) {
                    asColumnName = strColumn.substring(asStartIndex, asEndIndex).trim();
                }

            }
        }
        return new String[]{columnName, asColumnName};
    }
    private static String replaceCaculateColumns(String strColumn)
    {
        if(strColumn.indexOf("+") >= 0 || strColumn.indexOf("-") >= 0 || strColumn.indexOf("*") >= 0 || strColumn.indexOf("/") >= 0)
        {
            String regex = "[\\+\\-\\*\\/](.*)as";
            strColumn = strColumn.replaceAll(regex, " as");
        }
        return strColumn;
    }

    public static void main(String[] args) {
        String strColumn = "B.`id` AS id,\n" +
                "A.`sample_no` AS sample_no,\n" +
                "C.`subtask_name` AS subtask_name,\n" +
                "A.`steel_grade_mark` AS steel_grade_mark,\n" +
                "A.`sample_category` AS sample_category,\n" +
                "A.`furnace_no` AS furnace_no,\n" +
                "A.`sample_position_code` AS sample_position_code,\n" +
                "A.`sample_position_desc` AS sample_position_desc,\n" +
                "A.`sample_order_no` AS sample_order_no,\n" +
                "B.`sub_audit_status` AS flow_status,\n" +
                "A.`steel_grade_no` AS steel_grade_no,\n" +
                "A.`desulfurization_no` AS desulfurization_no,\n" +
                "A.`sample_status` AS sample_status,\n" +
                "B.`update_time` AS update_time,\n" +
                "B.`id` AS entrust_subtask_id,\n" +
                "C.`id` AS subtask_id";
        String[] columnStruct = getColumnStruct(strColumn);
        for (String s : columnStruct) {
            System.out.println(s);
        }
    }
}
